﻿Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient


Imports System.Runtime.InteropServices.Marshal
Imports System.Web
Imports System.Security.Principal
Imports System.Runtime.InteropServices
Imports System.Configuration
Imports System
Partial Class Paginas_UploadRut
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub proceso_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles proceso.Click
        Dim strscript As String
        Dim Rutinas As New DataServices.Rutinas
        Dim scriptblock1 As String
        Dim Destino As String

        If IsNothing(MyFile.PostedFile) Then
            Dim scriptblock As String = "<script language='javascript'>alert('Seleccione un archivo');</script>"
            Page.RegisterStartupScript("Launch_Report_Window", scriptblock)
            Exit Sub
        End If

        If MyFile.PostedFile.ContentLength = 0 Then
            Dim scriptblock As String = "<script language='javascript'>alert('No se cargó, archivo vacío ');</script>"
            Page.RegisterStartupScript("Launch_Report_Window", scriptblock)
            Exit Sub
        End If

        Dim nombreF As String = Path.GetFileName(MyFile.PostedFile.FileName)
        Dim nFile As String = Mid(nombreF, 1, Len(nombreF) - 4)
        Dim tFile As String = Mid(nombreF, Len(nombreF) - 3)
        If UCase(tFile) <> ".XLS" Then
            Destino = "alert('Tipo del archivo errado. Debe ser .XLS');"
            scriptblock1 = "<script language='javascript'>" & Destino & "</script>"
            Page.RegisterStartupScript("Launch_Report_Window", scriptblock1)
            Exit Sub
        End If
        Dim currentTime As System.DateTime = System.DateTime.Now
        Dim dfecha_1 As String = "_" & currentTime.Year & currentTime.Month & currentTime.Day & "_" & currentTime.Hour & currentTime.Minute & currentTime.Second
        Destino = Server.MapPath(Nothing) + "\Upload\" + nFile + dfecha_1 + tFile
        'Destino = Server.MapPath(Nothing) + "\Upload\" + nFile + tFile

        'Destino = Server.MapPath(Nothing) + "\Upload\" + Path.GetFileName(MyFile.PostedFile.FileName)
        'File1.PostedFile.SaveAs(Destino)

        'Dim Destino As String = Replace(Server.MapPath(Nothing), "\Paginas", "")
        'Destino = Destino + "\Upload\" + Path.GetFileName(MyFile.PostedFile.FileName)
        MyFile.PostedFile.SaveAs(Destino)
        Dim hoja() As String = GetExcelSheetNames(Destino)

        'If UBound(hoja) > 1 Then
        '    Destino = "alert('La planilla excel contiene mas de una hoja. Proceso Cancelado ');"
        '    scriptblock1 = "<script language='javascript'>" & Destino & "</script>"
        '    Page.RegisterStartupScript("Launch_Report_Window", scriptblock1)
        '    Exit Sub
        'End If


        Destino = excelToSql(Destino, hoja(0), "UploadRutConvenio", 1)
        'System.IO.File.Delete(Destino)
        Destino = "alert('" & Destino & "');"
        scriptblock1 = "<script language='javascript'>" & Destino & "</script>"
        Page.RegisterStartupScript("Launch_Report_Window", scriptblock1)

    End Sub


    Function excelToSql(ByVal FileName As String, ByVal SheetName As String, ByVal TableName As String, ByVal SP As Integer) As String
        'excelToSql = ""

        'Dim Rutinas As New DataServices.Rutinas
        'Dim j As Integer
        'Dim mensajeBD As String
        'Dim scriptblock1 As String
        'Dim destino As String
        'Dim Dias As Integer
        'xglosa.Text = ""

        'Try


        '    Dim LISTA_BD As String
        '    LISTA_BD = "RUT,VENDEDOR"

        '    Dim SQL As String = ConfigurationSettings.AppSettings("ConnectionString")
        '    Dim cmdDatabase As SqlCommand
        '    Dim contoSql As SqlConnection
        '    Dim contoExcel As OleDbConnection
        '    Dim cmd As OleDbCommand
        '    Dim Excel As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=YES;"""

        '    contoExcel = New OleDbConnection(Excel)
        '    contoExcel.Open()
        '    mensajeBD = ""
        '    cmd = New OleDbCommand("SELECT * FROM [" & SheetName.ToString & "]", contoExcel)
        '    Dim rdr As OleDbDataReader = cmd.ExecuteReader()
        '    Dim schema As DataTable = rdr.GetSchemaTable
        '    Dim name As String = ""

        '    Dim Lista_Col As String = ""
        '    For Each row As DataRow In schema.Rows
        '        Dim i As Integer = 0
        '        For Each col As DataColumn In schema.Columns

        '            If i = 0 Then
        '                Lista_Col = Lista_Col & Convert.ToString(row(0)) & ","
        '                name = name & "[" & Convert.ToString(row(0)) & "] nvarchar(255), "
        '            End If
        '            i = i + 1
        '        Next
        '    Next

        '    name = Mid(name, 1, Len(name) - 2)
        '    Lista_Col = Mid(Lista_Col, 1, Len(Lista_Col) - 1)
        '    Dim LISTA_BD_r() As String = LISTA_BD.Split(",")
        '    Dim Lista_Col_r() As String = Lista_Col.Split(",")

        '    Dim k As Integer
        '    Dim esta As Integer = 0
        '    Dim tit_aux As String = ""
        '    Dim mensaje As String = ""

        '    For k = 0 To UBound(LISTA_BD_r)
        '        tit_aux = LISTA_BD_r(k)
        '        For j = 0 To UBound(Lista_Col_r)
        '            If UCase(LISTA_BD_r(k)) = UCase(Lista_Col_r(j)) Then
        '                tit_aux = ""
        '            End If
        '        Next
        '        If tit_aux <> "" Then
        '            mensaje = mensaje & " , " & tit_aux
        '        End If
        '    Next

        '    If mensaje <> "" Then
        '        contoExcel.Close()
        '        mensaje = "LAS SIGUIENTES COLUMNAS FALTAN O EL NOMBRE EN LA PLANILLA ES INCORRECTO : " & mensaje
        '        destino = "alert('" & mensaje & "');"
        '        scriptblock1 = "<script language='javascript'>" & destino & "</script>"
        '        Page.RegisterStartupScript("Launch_Report_Window", scriptblock1)
        '        contoExcel.Close()
        '        contoExcel.Dispose()
        '        Exit Function

        '    End If

        '    Dim comandoSql As String
        '    comandoSql = "IF object_id('" & TableName & "') IS NOT NULL BEGIN    DROP TABLE " & TableName & " END "
        '    comandoSql = comandoSql & "Create table " & TableName & " (" & name & ")"
        '    contoSql = New SqlConnection(SQL)
        '    cmdDatabase = New SqlCommand(comandoSql, contoSql)
        '    contoSql.Open()
        '    cmdDatabase.ExecuteNonQuery()

        '    Using bulkCopy As New SqlBulkCopy(contoSql)
        '        bulkCopy.DestinationTableName = TableName
        '        bulkCopy.BulkCopyTimeout = 60
        '        bulkCopy.WriteToServer(rdr)
        '    End Using

        '    Dim Csql As String

        '    Csql = " "
        '    Csql = "update UploadRutConvenio set rut=REPLACE(LTRIM(REPLACE(rut, '0', ' ')), ' ', '0') "
        '    Csql = Csql & "update UploadRutConvenio set rut=replace(rut,'.','') "
        '    Csql = Csql & " update UploadRutConvenio set rut=replace(rut,',','') "
        '    Csql = Csql & " update UploadRutConvenio set rut=upper(rut) "

        '    Rutinas.EjecutarSql(Csql)



        '    Csql = "select vendedor as campo1 from UploadRutConvenio where vendedor not in (select tcodigo_pk from vendedores where cestado=1 )"

        '    If Busca(Csql, "Código Vendedor. ") = 1 Then
        '        xglosa.Text = "No existe Vendedor . Proceso cancelado "
        '        excelToSql = xglosa.Text
        '        contoExcel.Close()
        '        contoExcel.Dispose()
        '        Exit Function
        '    End If

        '    grilla.Visible = False


        '    Csql = " Select [RUT] + '-->' + convert(varchar(10),vendedor) as campo1 from UploadRutConvenio where DBO.VALIDADV([RUT])=1 "

        '    If Busca(Csql, "RUT Invalidos") = 1 Then
        '        xglosa.Text = "En la planilla hay  RUT no validos . Los RUT invalidos fueron excluidos del proceso.  "
        '        excelToSql = xglosa.Text
        '        grilla.Visible = True
        '        'contoExcel.Close()
        '        'contoExcel.Dispose()
        '        'Exit Function
        '    End If




        '    If SP = 1 Then
        '        Dim Rp As New gsonorad.ResponseValidator("", "")
        '        Rp.ExecuteDataset("SP_" & TableName, Nothing)

        '        If Not Rp.Errores Then
        '            Rp.Commit()
        '        Else
        '            mensajeBD = Rp.Mensaje
        '        End If
        '        Rp.Dispose()
        '    End If

        '    contoSql.Close()
        '    contoExcel.Close()
        '    contoExcel.Dispose()
        '    If mensajeBD = "" Then
        '        excelToSql = "Datos Transferidos"
        '    Else
        '        excelToSql = mensajeBD
        '    End If
        'Catch ex As Exception
        '    excelToSql = ex.Message
        'End Try

    End Function

    Public Function GetExcelSheetNames(ByVal excelFileName As String) As String()
        Dim con As OleDbConnection = Nothing
        Dim dt As DataTable = Nothing
        Dim conStr As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=") + excelFileName & ";Extended Properties=Excel 8.0;"
        con = New OleDbConnection(conStr)
        con.Open()
        dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        If dt Is Nothing Then
            Return Nothing
        End If
        Dim excelSheetNames As String() = New String(dt.Rows.Count - 1) {}
        Dim i As Integer = 0
        For Each row As DataRow In dt.Rows
            excelSheetNames(i) = row("TABLE_NAME").ToString()
            i += 1
        Next
        con.Close()
        con.Dispose()
        Return excelSheetNames
    End Function

    Function Busca(ByVal csql As String, ByVal titulo As String) As Integer
        Busca = 0
        Dim resultado As New DataSet
        Dim Rutinas As New DataServices.Rutinas

        resultado = Rutinas.ExtraeSql(csql)
        If resultado.Tables.Count <> 0 Then
            Call Rutinas.Grilla_Nolink(resultado.Tables(0), grilla, Titulo)
            grilla.Visible = True
            Busca = 1
        End If
    End Function

    Protected Sub excel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles excel.Click

        Dim csql As String

        Csql = " Select [RUT] + '-->' + convert(varchar(10),vendedor) as campo1 from UploadRutConvenio where DBO.VALIDADV([RUT])=1 "

        If Busca(Csql, "RUT Invalidos") = 1 Then
            xglosa.Text = "En la planilla hay  RUT no validos . Los RUT invalidos fueron excluidos del proceso.  "
            'xglosa.Text = xglosa.Text
            grilla.Visible = True
            'contoExcel.Close()
            'contoExcel.Dispose()
            'Exit Function
        End If


        Dim exp As New DataServices.Utilidades.ExportarExcel
        exp.Exportar(Me.grilla, Response)
        exp.Dispose()

    End Sub
End Class
